import numpy as np
import pandas as pd
from PIL import Image
import plotly.express as px
import plotly.graph_objects as go
import pyodbc as odbc
import plotly.express as px
import duckdb as db
import warnings
import dtale as dtl
warnings.filterwarnings("ignore")
# conn = odbc.connect('DRIVER={ODBC Driver 17 for SQL Server};''SERVER=XYZ;\
# ''DATABASE=XYZ;''Trusted_Connection=yes;')
# dframe = pd.read_sql_query("SELECT * FROM XYZ", conn)
# dframe.to_csv('temp_defect_summary.csv')
dframe = pd.read_csv('temp_defect_summary.csv')
import plotly.express as px
df = dframe
df = df.sort_values(by='measured_date')
fig = px.line(df, x="measured_date", y="defect_count", color='defect_location_category')
fig.show()
device_coordinate_map = {
'lower-left': [0.2, 0.2],
'mid-left': [0.2, 0.5],
'upper-left': [0.2, 0.8],
'lower-mid': [0.5, 0.2],
'mid': [0.5, 0.5],
'upper-mid': [0.5, 0.8],
'lower-right': [0.8, 0.2],
'mid-right': [0.8, 0.5],
'upper-right': [0.8, 0.8]
}
dt_lap = dframe.query("defect_location_category != 'misallgined'")
dt_lap["time_string"] = dt_lap["measured_date"].astype(str)
dt_lap['x'], dt_lap['y'] = zip(*list(dt_lap['defect_location_category'].map(device_coordinate_map).values))
img = Image.open('SmallCard.JPG')
# Create figure
fig = px.scatter(dt_lap, x="x", y="y",
size="defect_count", color="defect_location_category",animation_frame="time_string",\
animation_group="defect_location_category", height=425, width=800, hover_name="measured_date")
# Add images
fig.add_layout_image(
dict(
source=img,
x=0,
y=1,
sizex=1,
sizey=1,
sizing="contain",
layer="below"
)
)
# Set templates
fig.update_layout(
template="plotly_white",
xaxis=dict(range=[0,1], showgrid=False),
yaxis=dict(range=[0,1], showgrid=False)
)
fig.show()
dt_lap_grouped = dt_lap[['defect_location_category','defect_count','x','y']]
dt_lap_grouped = dt_lap_grouped.groupby(by='defect_location_category').agg({'defect_count':'sum','x':'max','y':'max'})\
.reset_index()
img = Image.open('SmallCard.JPG')
# Create figure
fig = px.scatter(dt_lap_grouped, x="x", y="y",
size="defect_count", color="defect_location_category", height=350, width=800)\
.update_traces(marker=dict(color='red'))
# Add images
fig.add_layout_image(
dict(
source=img,
x=0,
y=1,
sizex=1,
sizey=1,
sizing="contain",
layer="below"
)
)
# Set templates
fig.update_layout(
template="plotly_white",
xaxis=dict(range=[0,1], showgrid=False),
yaxis=dict(range=[0,1], showgrid=False)
)
fig.show()
from sklearn.preprocessing import MinMaxScaler
dt_lap_grouped['defect_count_scaled'] = MinMaxScaler((0.1,0.85))\
.fit_transform(np.array(dt_lap_grouped['defect_count']).reshape(-1,1))
device_coordinate_map_rect = {
'lower-left': [0, 0.33, 0, 0.307],
'mid-left': [0, 0.33, 0.307, 0.641],
'upper-left': [0, 0.33, 0.641, 1],
'lower-mid': [0.33, 0.65625, 0, 0.307],
'mid': [0.33, 0.65625, 0.307, 0.641],
'upper-mid': [0.33, 0.65625, 0.641, 1],
'lower-right': [0.65625, 1, 0, 0.307],
'mid-right': [0.65625, 1, 0.307, 0.641],
'upper-right': [0.65625, 1, 0.641, 1],
}
dt_lap_grouped['x0'], dt_lap_grouped['x1'], dt_lap_grouped['y0'], dt_lap_grouped['y1'] = \
zip(*list(dt_lap_grouped['defect_location_category']\
.map(device_coordinate_map_rect).values))
img = Image.open('SmallCard.JPG')
# Create figure
fig = px.scatter(dt_lap_grouped, x="x", y="y", size="defect_count",
hover_data={"defect_count":True, "x":False, "y":False, 'defect_location_category':False},
color="defect_location_category", height=350, width=686)\
.update_traces(marker=dict(color='black'))
for i in dt_lap_grouped.values:
fig.add_vrect(x0=i[5], x1=i[6], y0=i[7], y1=i[8], fillcolor="red", opacity=i[4])
# Add images
fig.add_layout_image(
dict(
source=img,
x=0,
y=1,
sizex=1,
sizey=1,
sizing="contain",
layer="below"
)
)
# Set templates
fig.update_layout(
template="plotly_white",
xaxis=dict(range=[0,1], showgrid=False),
yaxis=dict(range=[0,1], showgrid=False),
showlegend=False
)
fig.add_hline(y=0, line_color="red")
fig.add_hline(y=0.307, line_color="red")
fig.add_hline(y=0.641, line_color="red")
fig.add_vline(x=0, line_color="red")
fig.add_vline(x=0.33, line_color="red")
fig.add_vline(x=0.65625, line_color="red")
fig.show()
temp = db.sql("SELECT defect_location_category, SUM(defect_count) defect_count, \
FROM dframe GROUP BY defect_location_category").df()
miaslligned_percent = temp[temp['defect_location_category']=='misallgined']['defect_count'].sum()/\
temp[temp['defect_location_category']!='misallgined']['defect_count'].sum()
print(f'Overall {np.round(miaslligned_percent*100,2)}% Cards of Size 2 were rejected due to misallignment')
Overall 5.07% Cards of Size 2 were rejected due to misallignment
dframe_cs1 = pd.read_csv('temp_defect_summary_medium.csv')
#dframe_cs1['defect_location_category'] =
dframe_cs1 = \
db.sql("SELECT measurementtime, loc_x, loc_y,\
CASE WHEN (loc_x=0 and loc_y=0) then 'misallgined'\
WHEN (loc_x>=-50 and loc_x<=383) and (loc_y>=-200 and loc_y<=116) then 'lower-left'\
WHEN (loc_x>=-50 and loc_x<=383) and (loc_y>116 and loc_y<=434) then 'mid-left'\
WHEN (loc_x>=-50 and loc_x<=383) and (loc_y>434 and loc_y<=750) then 'upper-left'\
WHEN (loc_x>383 and loc_x<=817) and (loc_y>=-200 and loc_y<=116) then 'lower-mid'\
WHEN (loc_x>383 and loc_x<=817) and (loc_y>116 and loc_y<=434) then 'mid'\
WHEN (loc_x>383 and loc_x<=817) and (loc_y>434 and loc_y<=750) then 'upper-mid'\
WHEN (loc_x>817 and loc_x<=1250) and (loc_y>=-200 and loc_y<=116) then 'lower-right'\
WHEN (loc_x>817 and loc_x<=1250) and (loc_y>116 and loc_y<=434) then 'mid-right'\
WHEN (loc_x>817 and loc_x<=1250) and (loc_y>434 and loc_y<=750) then 'upper-right'\
ELSE 'misallgined'\
END AS defect_location_category\
FROM dframe_cs1").df()
dframe_cs1['measurementtime'] = pd.to_datetime(dframe_cs1['measurementtime'])
dframe_cs1['measured_date'] = dframe_cs1.measurementtime.dt.date
dframe_cs1_summ_daily = dframe_cs1[['measured_date','defect_location_category']]\
.groupby(by=['measured_date','defect_location_category'])\
.agg(defect_count = ('measured_date', 'count')).reset_index()
dframe_cs1_summ_daily = dframe_cs1_summ_daily.sort_values(by='measured_date')
fig = px.line(dframe_cs1_summ_daily, x="measured_date", y="defect_count", color='defect_location_category')
fig.show()
device_coordinate_map = {
'lower-left': [0.2, 0.2],
'mid-left': [0.2, 0.5],
'upper-left': [0.2, 0.8],
'lower-mid': [0.5, 0.2],
'mid': [0.5, 0.5],
'upper-mid': [0.5, 0.8],
'lower-right': [0.8, 0.2],
'mid-right': [0.8, 0.5],
'upper-right': [0.8, 0.8]
}
dt_lap = dframe_cs1_summ_daily.query("defect_location_category != 'misallgined'")
dt_lap["time_string"] = dt_lap["measured_date"].astype(str)
dt_lap['x'], dt_lap['y'] = zip(*list(dt_lap['defect_location_category'].map(device_coordinate_map).values))
dt_lap_grouped = dt_lap[['defect_location_category','defect_count','x','y']]
dt_lap_grouped = dt_lap_grouped.groupby(by='defect_location_category').agg({'defect_count':'sum','x':'max','y':'max'})\
.reset_index()
from sklearn.preprocessing import MinMaxScaler
dt_lap_grouped['defect_count_scaled'] = MinMaxScaler((0.1,0.85))\
.fit_transform(np.array(dt_lap_grouped['defect_count']).reshape(-1,1))
device_coordinate_map_rect = {
'lower-left': [0, 0.33, 0, 0.307],
'mid-left': [0, 0.33, 0.307, 0.641],
'upper-left': [0, 0.33, 0.641, 1],
'lower-mid': [0.33, 0.65625, 0, 0.307],
'mid': [0.33, 0.65625, 0.307, 0.641],
'upper-mid': [0.33, 0.65625, 0.641, 1],
'lower-right': [0.65625, 1, 0, 0.307],
'mid-right': [0.65625, 1, 0.307, 0.641],
'upper-right': [0.65625, 1, 0.641, 1],
}
dt_lap_grouped['x0'], dt_lap_grouped['x1'], dt_lap_grouped['y0'], dt_lap_grouped['y1'] = \
zip(*list(dt_lap_grouped['defect_location_category']\
.map(device_coordinate_map_rect).values))
img = Image.open('MedCard.JPG')
fig = px.scatter(dt_lap_grouped, x="x", y="y", size="defect_count",
hover_data={"defect_count":True, "x":False, "y":False, 'defect_location_category':False},
color="defect_location_category", height=360, width=578)\
.update_traces(marker=dict(color='black'))
for i in dt_lap_grouped.values:
fig.add_vrect(x0=i[5], x1=i[6], y0=i[7], y1=i[8], fillcolor="red", opacity=i[4])
# Add images
fig.add_layout_image(
dict(
source=img,
x=0,
y=1,
sizex=1,
sizey=1,
sizing="contain",
layer="below"
)
)
# Set templates
fig.update_layout(
template="plotly_white",
xaxis=dict(range=[0,1], showgrid=False),
yaxis=dict(range=[0,1], showgrid=False),
showlegend=False
)
fig.add_hline(y=0, line_color="red")
fig.add_hline(y=0.307, line_color="red")
fig.add_hline(y=0.641, line_color="red")
fig.add_vline(x=0, line_color="red")
fig.add_vline(x=0.33, line_color="red")
fig.add_vline(x=0.65625, line_color="red")
fig.show()
temp = db.sql("SELECT defect_location_category, SUM(defect_count) defect_count, \
FROM dframe_cs1_summ_daily GROUP BY defect_location_category").df()
miaslligned_percent = temp[temp['defect_location_category']=='misallgined']['defect_count'].sum()/\
temp[temp['defect_location_category']!='misallgined']['defect_count'].sum()
print(f'Overall {np.round(miaslligned_percent*100,2)}% Cards of Size 1 were rejected due to misallignment')
Overall 2.04% Cards of Size 1 were rejected due to misallignment